package org.nokatag.dbgrid;

import java.io.BufferedReader;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.noka.gson.Gson;
import org.noka.gson.reflect.TypeToken;
import org.nokatag.password.AESPlus;
import org.nokatag.system.BugInfoWrint;
import org.nokatag.system.Concest;
import org.nokatag.system.DBCon;

public class NDBGridCellsDBUtil {
	private static Boolean isCreateTable = false;//是否已经建表
	//-----------------SQLServer 200数据库----------------------------------
	private static final String SQLServer_SQL="create table "+Concest.DBGRID_SET_TABLE+" ("
		+Concest.UP_FILEID+"               		varchar(200)         not null,"
		+Concest.UP_FILEDOCUMENT+"             	TEXT                null,"
		+"constraint PK_"+Concest.DBGRID_SET_TABLE+" primary key nonclustered ("+Concest.UP_FILEID+")"
		+")";
	//-----------------Oracle数据库----------------------------------
	private static final String Oracle_SQL="create table "+Concest.DBGRID_SET_TABLE+"  ("
		+Concest.UP_FILEID+"               		VARCHAR(200)        not null,"
		+Concest.UP_FILEDOCUMENT+"              CLOB,"
		+"constraint PK_"+Concest.DBGRID_SET_TABLE+" primary key ("+Concest.UP_FILEID+")"
		+")";
	private static final String MySQL_SQL="create table "+Concest.DBGRID_SET_TABLE+" ("
		+Concest.UP_FILEID+"               		varchar(200) 		not null,"
		+Concest.UP_FILEDOCUMENT+"              TEXT,"
		+"primary key ("+Concest.UP_FILEID+")"
		+")";
	private static final String PGSQL_SQL="create table "+Concest.DBGRID_SET_TABLE+" ("
		+Concest.UP_FILEID+"               VARCHAR(200)         not null,"
		+Concest.UP_FILEDOCUMENT+"            TEXT           null"
		+")";
	private static final String DB2_SQL="create table "+Concest.DBGRID_SET_TABLE+"  ("
		+Concest.UP_FILEID+"               VARCHAR(200)        not null,"
		+Concest.UP_FILEDOCUMENT+"             CLOB(1 G)"
		+")";

	private static final String Other_SQL="create table "+Concest.DBGRID_SET_TABLE+"  ("
	+Concest.UP_FILEID+"               VARCHAR(200)        not null,"
	+Concest.UP_FILEDOCUMENT+"             CLOB(8000)"
	+")";
	//-----------------创建数据库-------------------------------------
	public  boolean CeeateFileTable(HttpServletRequest request){
		Statement sta= null;
		try{
			if(NDBGridCellsDBUtil.isCreateTable){
				return false;
			}
			if(!DBTableISCreateed(request)){
				Connection con = DBCon.getCon();
				DatabaseMetaData dmd = con.getMetaData();
				sta= con.createStatement();
				String DataBaseProductName = dmd.getDatabaseProductName();
				if(DataBaseProductName.equalsIgnoreCase("Oracle")){
					BugInfoWrint.Print("开始建表："+Oracle_SQL);
					sta.execute(Oracle_SQL);
				}else if(DataBaseProductName.equalsIgnoreCase("Microsoft SQL Server")){
					BugInfoWrint.Print("开始建表："+SQLServer_SQL);
					sta.execute(SQLServer_SQL);
				}else if(DataBaseProductName.equalsIgnoreCase("MySQL")){
					BugInfoWrint.Print("开始建表："+MySQL_SQL);
					sta.execute(MySQL_SQL);
				}else if(DataBaseProductName.equalsIgnoreCase("PostgreSQL")){
					BugInfoWrint.Print("开始建表："+PGSQL_SQL);
					sta.execute(PGSQL_SQL);
				}else if(DataBaseProductName.startsWith("DB2")){
					BugInfoWrint.Print("开始建表："+DB2_SQL);
					sta.execute(DB2_SQL);
				}else if(DataBaseProductName.equalsIgnoreCase("Greenplum")){
					BugInfoWrint.Print("开始建表："+PGSQL_SQL);
					sta.execute(PGSQL_SQL);
				}else{
					BugInfoWrint.Print("开始建表："+Other_SQL);
					sta.execute(Other_SQL);
				}
				if(!con.getAutoCommit()){//非自动提交模式
					con.commit();//提交更改
				}
				sta.close();
				NDBGridCellsDBUtil.isCreateTable=true;
				return true;
			}else{
				BugInfoWrint.Print("数据表已经存在");
				return false;
			}
		}catch(Exception se){
			return false;
		}finally{
			if(null!=sta){
				try{sta.close();}catch(Exception se){}
			}
		}
	}
	public static Boolean isText(){
		try{
			Connection con = DBCon.getCon();
			DatabaseMetaData dmd = con.getMetaData();
			String DataBaseProductName = dmd.getDatabaseProductName();
			if(DataBaseProductName.equalsIgnoreCase("Microsoft SQL Server") || DataBaseProductName.equalsIgnoreCase("MySQL") || DataBaseProductName.equalsIgnoreCase("PostgreSQL") ){
				return true;
			}
		}catch(Exception se){
		}
		return false;
	}
	//---------------检测数据表存在----------------------------------
	public  boolean DBTableISCreateed(HttpServletRequest request){
		Statement sta = null;
		try{
			Connection con = DBCon.getCon();
			if(con!=null)
				sta = con.createStatement();
			if(sta!=null){
				ResultSet rs = sta.executeQuery("select "+Concest.UP_FILEID+" from "+Concest.DBGRID_SET_TABLE+" WHERE 1<>1");
				rs.close();
				sta.close();
				NDBGridCellsDBUtil.isCreateTable=true;
				return true;
			}
		}catch(Exception se){
			return false;
		}finally{
			if(null!=sta){
				try{sta.close();}catch(Exception se){}
			}
		}
		return false;
	}
	/**
	 * <p>@Description 清除</p>
	 * <p>author rebin </p>
	 * <p>@Time 上午1:55:52</p>
	 * <p>xiefangjian@163.com</p>
	 * @param key
	 */
	public void ClearSet(String key,HttpServletRequest request){
		PreparedStatement ps = null;
		try{
			Connection conn = DBCon.getCon();
			String sql="DELETE  FROM "+Concest.DBGRID_SET_TABLE+" WHERE "+Concest.UP_FILEID+" =? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1,key);
			ps.executeUpdate();
			if(!conn.getAutoCommit()){//非自动提交模式
				conn.commit();//提交更改
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=ps){
				try{ps.close();}catch(Exception se){}
			}
		}
	}
	/**
	 * <p>@Description 获取配置</p>
	 * <p>author rebin </p>
	 * <p>@Time 上午11:08:27</p>
	 * <p>xiefangjian@163.com</p>
	 * @param key
	 * @param request
	 * @return
	 */
	public  String getSet(String key,HttpServletRequest request){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			Connection conn = DBCon.getCon();
			ps = conn.prepareStatement("SELECT "+Concest.UP_FILEDOCUMENT+" FROM "+Concest.DBGRID_SET_TABLE+"  WHERE "+Concest.UP_FILEID+"=?");
			ps.setString(1,key);
			rs = ps.executeQuery();
			if (rs.next()) {
				if(isText()){
					String scd = new String(rs.getString(Concest.UP_FILEDOCUMENT));
					return  AESPlus.decrypt(scd,Concest.SQLKEY);//解密
				}else{
					String scd = toCharArray(rs.getCharacterStream(Concest.UP_FILEDOCUMENT));
					return  AESPlus.decrypt(scd,Concest.SQLKEY);//解密
				}
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=ps){
				try{ps.close();}catch(Exception se){}
			}
			if(null!=rs){
				try{rs.close();}catch(Exception se){}
			}
		}
		return null;
	}
	/**
	 * <p>@Description 保存配置</p>
	 * <p>author rebin </p>
	 * <p>@Time 上午11:15:21</p>
	 * <p>xiefangjian@163.com</p>
	 * @param key
	 * @param cells
	 * @param request
	 */
	public void SaveSet(String key,String cells,HttpServletRequest request){
		PreparedStatement ps = null;
		try{
			Connection conn = DBCon.getCon();
			ClearSet(key,request);//清除旧的配置
			ps = conn.prepareStatement("insert into "+Concest.DBGRID_SET_TABLE+"("+Concest.UP_FILEID+","+Concest.UP_FILEDOCUMENT+") values (?,?)");
			ps.setString(1, key);
			if(isText()){
				ps.setString(2,AESPlus.encrypt(cells,Concest.SQLKEY));
			}else{
				String bs = AESPlus.encrypt(cells,Concest.SQLKEY);
				StringReader reader = new StringReader(bs); 
				ps.setCharacterStream(2, reader, bs.length());
			}
			ps.executeUpdate();
			if(!conn.getAutoCommit()){//非自动提交模式
				conn.commit();//提交更改
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=ps){
				try{ps.close();}catch(Exception se){}
			}
		}
	}
	/**
	 * <p>@Description 清除配置</p>
	 * <p>author rebin </p>
	 * <p>@Time 上午11:17:52</p>
	 * <p>xiefangjian@163.com</p>
	 * @param key
	 * @param newCells
	 * @param request
	 * @return
	 */
	public boolean clearCells(String key,String newCells,HttpServletRequest request){
		Gson gson = new Gson();
		String setCells = getSet(key,request);
		if(null==newCells || null==setCells){
			return false;
		} 
		List<CellsItem> oldList = gson.fromJson(setCells, new TypeToken<List<CellsItem>>(){}.getType());
		List<CellsItem> newList = gson.fromJson(newCells, new TypeToken<List<CellsItem>>(){}.getType());
		int max = oldList.size();
		if(max!=newList.size()){//列数不一至则清除
			ClearSet(key,request);
			return false;
		}
		else{
			for(int i=0;i<max;i++){
				try{
					if(!oldList.get(i).getTitle().equals(newList.get(i).getTitle())){//两个列名不一至则清除
						ClearSet(key,request);
						return false;
					}
				}catch(Exception se){}
			}
		}
		return true;
	}
	//=======================折行设置====================================================
	/**
	 * <p>@Description 保存折行设置</p>
	 * <p>author rebin </p>
	 * <p>@Time 下午3:43:48</p>
	 * <p>xiefangjian@163.com</p>
	 * @param key
	 * @param request
	 */
	public void SaveFoldingLine(String key,HttpServletRequest request,boolean line){
		PreparedStatement ps = null;
		try{
			Connection conn = DBCon.getCon();
			ClearFoldingLine(key,request);//清除老数据
			ps = conn.prepareStatement("insert into "+Concest.DBGRID_SET_TABLE+"("+Concest.UP_FILEID+") values (?)");
			if(line){
				ps.setString(1,key+"/Line_true");//插入
			}else{
				ps.setString(1,key+"/Line_false");//插入
			}
			ps.executeUpdate();
			if(!conn.getAutoCommit()){//非自动提交模式
				conn.commit();//提交更改
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=ps){
				try{ps.close();}catch(Exception se){}
			}
		}
	}
	/**
	 * <p>@Description 清除折行设置</p>
	 * <p>author rebin </p>
	 * <p>@Time 下午3:44:34</p>
	 * <p>xiefangjian@163.com</p>
	 * @param key
	 * @param request
	 */
	public void ClearFoldingLine(String key,HttpServletRequest request){
		PreparedStatement ps = null;
		try{
			Connection conn = DBCon.getCon();
			String sql="DELETE  FROM "+Concest.DBGRID_SET_TABLE+" WHERE "+Concest.UP_FILEID+" like ? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1,key+"/Line_%");
			ps.executeUpdate();
			if(!conn.getAutoCommit()){//非自动提交模式
				conn.commit();//提交更改
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=ps){
				try{ps.close();}catch(Exception se){}
			}
		}
	}
	/**
	 * <p>@Description 是否有折行</p>
	 * <p>author rebin </p>
	 * <p>@Time 下午3:46:38</p>
	 * <p>xiefangjian@163.com</p>
	 * @param key
	 * @param request
	 * @return
	 */
	public  String getFoldingLine(String key,HttpServletRequest request){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			Connection conn = DBCon.getCon();
			ps = conn.prepareStatement("SELECT "+Concest.UP_FILEID+" FROM "+Concest.DBGRID_SET_TABLE+"  WHERE "+Concest.UP_FILEID+" like ?");
			ps.setString(1,key+"/Line_%");
			rs = ps.executeQuery();
			if (rs.next()) {
				String st = rs.getString(Concest.UP_FILEID);
				if(null!=st){
					if(st.endsWith("true")){
						return "true";
					}else{
						return "false";
					}
				}
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=ps){
				try{ps.close();}catch(Exception se){}
			}
			if(null!=rs){
				try{rs.close();}catch(Exception se){}
			}
		}
		return null;
	}
	
	private  String toCharArray(Reader r){
		BufferedReader bufread  = null;
		StringBuffer sb = new StringBuffer();
		try{
			bufread = new BufferedReader(r);
			String read;
			while ((read = bufread.readLine()) != null) {
				sb.append(read);
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=bufread){
				try{bufread.close();}catch(Exception e){}
			}
			if(null!=r){
				try{r.close();}catch(Exception e){}
			}
		}
		return sb.toString();
	}
	
}
